-- EXEC sp_get_DocumentosPendientes_ByTipoDoc 1,'0000000000','1,2','10,10','0000'

ALTER PROCEDURE sp_get_DocumentosPendientes_ByTipoDoc
    @nDestEstado INT ,
    @cPerCodigo VARCHAR(10) ,
    @cTipoDoc VARCHAR(20) ,
    @cDocEstado VARCHAR(20) ,
    @cDocCodigo VARCHAR(20) = 'ALL'
AS 
    BEGIN  

        DECLARE @sql VARCHAR(MAX)

        SET @sql = '

        SELECT DISTINCT
                p.cPerApellido + SPACE(2) + p.cPerNombre AS PerDestino ,
                p.cPerCodigo AS CodPerDestino ,
                p2.cPerApellido + SPACE(2) + p2.cPerNombre AS PerRemite ,
                p2.cPerCodigo AS CodPerRemite ,
                d.nDocTipo ,
                d.nEstado ,
                CONVERT(VARCHAR(5000), dc.tDocConContenido) ,
                d.cDocObserv ,
                dc.nDocConTipo ,
                CONVERT(CHAR(11), dv.dFechaIni) AS Fecha ,
                di.cDocNDoc ,
                duo.nUniOrgCodigo ,
                d.cDocCodigo ,
                dpe.nUniOrgCodigo AS UODestino ,
                LEFT(C.cConDescripcion, 9) AS Tipo
        FROM    dbo.DocUniOrg duo
                INNER JOIN dbo.Documentos d ON duo.cDocCodigo = d.cDocCodigo
                INNER JOIN dbo.DocIdentifica di ON d.cDocCodigo = di.cDocCodigo
                INNER JOIN dbo.DocPersona dp ON d.cDocCodigo = dp.cDocCodigo
                INNER JOIN dbo.DocContenido dc ON d.cDocCodigo = dc.cDocCodigo
                INNER JOIN dbo.Persona p ON dp.cPerCodigo = p.cPerCodigo
                INNER JOIN dbo.DocPersona dp2 ON d.cDocCodigo = dp2.cDocCodigo
                INNER JOIN dbo.Persona p2 ON dp2.cPerCodigo = p2.cPerCodigo
                INNER JOIN dbo.DocVigencia dv ON d.cDocCodigo = dv.cDocCodigo
                INNER JOIN dbo.DocPerEdit dpe ON dp.cDocCodigo = dpe.cDocCodigo
                                                 AND dp.cPerCodigo = dpe.cPerCodigo
                INNER JOIN dbo.Constante C ON d.nDocTipo = C.nConValor
        WHERE   ( dp.nDocPerTipo = 2 )
                AND ( d.nEstado IN ( ' + @cDocEstado + ' ) )
                AND ( p.cPerCodigo IN ( ''' + @cPerCodigo + ''' ) )
                AND ( dp2.nDocPerTipo = 1 )
                AND ( duo.nDocUniOrgEstado = ' + CAST(@nDestEstado AS VARCHAR(2)) + ')
                AND ( dc.nDocConTipo IN ( ' + @cTipoDoc + ' ) )
                AND ( dpe.nDocPerEdiTipo IN ( 1006, 1007 ) )
                AND DUO.nDocUniOrgTipo = 1
                AND DI.nDocTipoNum = 1
                AND ( C.nConCodigo = 1063 ) '
        IF @cDocCodigo <> 'ALL' 
            BEGIN
                SET @sql = @sql + '	AND ( d.cDocCodigo = ''' + @cDocCodigo + ''' )'
            END	

        SET @sql = @sql + ' ORDER BY dc.nDocConTipo , Fecha '

		PRINT (@sql)
    END